﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CaseReivew.Model;
using CaseReview.Model;
using CaseReview.SQLServerDAL;

public partial class _Default : System.Web.UI.Page {
    //public static string _connStr;
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    //protected string GetConnString() {
    //    ConnectionStringSettingsCollection connectionStringSettingsCollection =
    //        System.Web.Configuration.WebConfigurationManager.ConnectionStrings as ConnectionStringSettingsCollection;
    //    ConnectionStringSettings connectionStringSettings = connectionStringSettingsCollection["dbLocalConnectionString"];
    //    return connectionStringSettings.ConnectionString;
    //}

    private bool UploadExcelFile(FileUpload fileUpload)
    {
        bool flag = false;
        bool fileAllow = false;
        string[] allowExtensions = { ".xls", ".xlsx" };
        string path = HttpContext.Current.Request.MapPath("~/upload/");
        if (fileUpload.HasFile)
        {
            string fileExtension = System.IO.Path.GetExtension(fileUpload.FileName).ToLower();
            if (allowExtensions.Contains(fileExtension))
                fileAllow = true;

            if (fileAllow)
            {
                try {
                    string[] files = Directory.GetFiles(path);
                    foreach (string file in files) {
                        File.Delete(file);
                    }

                    fileUpload.SaveAs(path + fileUpload.FileName);
                    Response.Write("<script>alert(\"File Upload Successful!\")</script>");
                    flag = true;
                }
                catch (Exception e)
                {
                    flag = false;
                }
            }
            else
            {
                Response.Write("<script>alert(\"Only allow Excel File!\")</script>");
                flag = false;
            }
        }
        else
        {
            Response.Write("<script>alert(\"Please select Excel file!\")</script>");
            flag = false;

        }
        return flag;
    }

    public DataSet ExcelDataSource(string filePath,string sheetName) {
        string connStr = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath+ ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
        OleDbConnection oleDbConnection = new OleDbConnection(connStr);
        oleDbConnection.Open();
        string sql = "select *from [" + sheetName + "]";
        OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(sql,connStr);
        DataSet dataSet = new DataSet();
        oleDbDataAdapter.Fill(dataSet);
        oleDbConnection.Close();
        return dataSet;
    }

    public ArrayList ExcelSheetName(string filePath) {
        ArrayList arrayList = new ArrayList();
        string connStr = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
        OleDbConnection oleDbConnection = new OleDbConnection(connStr);
        oleDbConnection.Open();
        DataTable sheetNames = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
        oleDbConnection.Close();
        foreach (DataRow dataRow in sheetNames.Rows) {
            arrayList.Add(dataRow[2]);
        }
        return arrayList;
    }
    protected void Upload_Click(object sender, EventArgs e)
    {
        bool b = UploadExcelFile(fileUpload);
        if (!b)
        {
            return;
        }
        string name = fileUpload.FileName;
        string filePath = Server.MapPath("~/upload/") + name;
        DataSet dataSet = ExcelDataSource(filePath, ExcelSheetName(filePath)[1].ToString());
        DataTable dataTable = dataSet.Tables[0];
        dataTable.Rows.RemoveAt(0);
        int count = dataTable.Rows.Count;
        List<int> random = GetRandomNum(count, int.Parse(txtNum.Text));
        //_connStr = GetConnString();
        //using (SqlConnection conn = new SqlConnection(_connStr))
        //{   
        //    conn.Open();
        //    string SQL_AddCaseTemp = "INSERT INTO [CaseReview].[dbo].[CaseInfo]([CaseId],[Title],[Age],[Labor],[Idle],[Owner],[Severity],[Phase],[ServiceLevel],[Type],[CountOfOwnership],[Status],[date],[Reviewer],[ReviewerName])VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}') ";
        //    for (int i = 0; i < random.Count; i++) {
        //        string caseId = dataTable.Rows[random[i]][0].ToString().Trim();
        //        string title = dataTable.Rows[random[i]][1].ToString().Trim();
        //        string age = dataTable.Rows[random[i]][2].ToString().Trim();
        //        string labor = dataTable.Rows[random[i]][3].ToString().Trim();
        //        string idle = dataTable.Rows[random[i]][4].ToString().Trim();
        //        string owner = dataTable.Rows[random[i]][5].ToString().Trim();
        //        string severity = dataTable.Rows[random[i]][6].ToString().Trim();
        //        string phase = dataTable.Rows[random[i]][7].ToString().Trim();
        //        string servicelevel = dataTable.Rows[random[i]][8].ToString().Trim();
        //        string type = dataTable.Rows[random[i]][9].ToString().Trim();
        //        string countOfOwnership = dataTable.Rows[random[i]][10].ToString().Trim();
        //        string reviewerName = "kirinx";
        //        int status = 0;
        //        DateTime dateTime = DateTime.Now;
        //        int reviewer = 0;
        //        string SQL_AddCase = string.Format(SQL_AddCaseTemp, caseId, title, age, labor, idle, owner, severity, phase, servicelevel, type, countOfOwnership, status, dateTime, reviewer, reviewerName);
        //        SqlCommand sqlCommand = new SqlCommand(SQL_AddCase,conn);
        //        sqlCommand.ExecuteNonQuery();
        //    }
        //    conn.Close();
        //});

        for (int i = 0; i < random.Count; i++) {
            CaseInfo caseInfo = new CaseInfo();
            caseInfo.CaseId = dataTable.Rows[random[i]][0].ToString().Trim();
            caseInfo.Title = dataTable.Rows[random[i]][1].ToString().Trim().Replace("\'","");
            caseInfo.Age = dataTable.Rows[random[i]][2].ToString().Trim();
            caseInfo.Labor = dataTable.Rows[random[i]][3].ToString().Trim();
            caseInfo.Idle = dataTable.Rows[random[i]][4].ToString().Trim();
            caseInfo.Owner = dataTable.Rows[random[i]][5].ToString().Trim();
            caseInfo.Severity = dataTable.Rows[random[i]][6].ToString().Trim();
            caseInfo.Phase = dataTable.Rows[random[i]][7].ToString().Trim();
            caseInfo.ServicelLevel = dataTable.Rows[random[i]][8].ToString().Trim();
            caseInfo.Type = dataTable.Rows[random[i]][9].ToString().Trim();
            caseInfo.CountOfOwner = dataTable.Rows[random[i]][10].ToString().Trim();
            caseInfo.ReviewerName = "kirinx";
            caseInfo.Status = 0;
            caseInfo.Reviewer = 0;
            caseInfo.Date = DateTime.Now;
            BusinessLogic.GetInstance().InsertCase(caseInfo);
        }
        lnkNext.Enabled = true;

        //update ReviewResult db status -> 1
        IList<ReviewResultInfo> resultInfos = BusinessLogic.GetInstance().GetTotalResultsByStatus(0);
        foreach (ReviewResultInfo reviewResultInfo in resultInfos) {
            reviewResultInfo.Status = 1;
            BusinessLogic.GetInstance().UpdateResult(reviewResultInfo,reviewResultInfo.CaseId);
        }

    }

    public List<int> GetRandomNum(int size, int res) {
        List<int > container = new List<int>(size);
        List<int> result = new List<int>(res);
        Random random = new Random();
        for (int i = 0; i < size; i++) {
            container.Add(i);
        }
        int index = 0;
        int value = 0;
        for (int i = 0; i < res; i++) {
            index = random.Next(0, container.Count);
            value = container[index];
            result.Add(value);
            container.RemoveAt(index);
        }
        return result;
    }
  

}
